﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Dapper;
using DapperExtensions;
using ExaminationSys.DataAccess.Entity;
using SQLinq;
using SQLinq.Dapper;

namespace ExaminationSys.DataAccess.Repository
{
    public class StudentTestQuestionRepository : Repository<StudentTestQuestion>
    {
        public StudentTestQuestionRepository(SqlConnection context)
            : base(context)
        {
            db = context;
        }
        private SqlConnection db;
        public override StudentTestQuestion GetById(dynamic id)
        {
            return base.ObjectSet.Get<StudentTestQuestion>(db, id, null, null);
        }
    }

    public class StudentTestQuestionService
    {
        private StudentTestQuestionService()
        {
        }
        public StudentTestQuestion Get(Guid id)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.StudentTestQuestion.GetById(id);
                return result;

            }
        }

        public bool CheckStudentPaper(string questionid, string testhistoryid)
        {
            using (var db = DataContent.DBServer())
            {
                var pg = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
                pg.Predicates.Add(Predicates.Field<StudentTestQuestion>(f => f.QuestionID, Operator.Eq, questionid));
                pg.Predicates.Add(Predicates.Field<StudentTestQuestion>(f => f.TestHistoryID, Operator.Eq, testhistoryid));
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.StudentTestQuestion.GetList(pg).FirstOrDefault();
                return result != null;
            }
        }

        public List<StudentTestQuestion> GetAll()
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                var result = work.StudentTestQuestion.GetList();
                return result.ToList();
            }
        }

        public bool Delete(List<string> ids)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"delete from StudentTestQuestion where ID=@TmpId",
                                         new[] { new { TmpId = id } });
                    if (retCode <= 0)
                        break;
                }
                flag = true;
            }
            return flag;
        }

        public bool Lock(List<string> ids, int _lock)
        {
            if (ids == null || ids.Count == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                foreach (var id in ids)
                {
                    retCode = db.Execute(@"update StudentTestQuestion set Lock=@lk where ID=@TmpId",
                                         new[] { new { lk = _lock, TmpId = id } });
                    if (retCode <= 0)
                    {
                        break;
                    }
                }
                flag = true;
            }
            return flag;
        }

        public bool Update(StudentTestQuestion entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.StudentTestQuestion.Update(entity);
            }
        }
        public dynamic Insert(StudentTestQuestion entity)
        {
            using (var db = DataContent.DBServer())
            {
                IUnitOfWork work = new UnitOfWork(db);
                return work.StudentTestQuestion.Insert(entity, null);
            }
        }

        public List<StudentTestQuestion> GetListByPage(string sqlwhere, string orderbyFiled, int pagesize, int pageindex, out int totalPage, out int totalRecord)
        {
            if (string.IsNullOrWhiteSpace(orderbyFiled))
                throw new ArgumentNullException("orderbyFiled");

            if (string.IsNullOrWhiteSpace(sqlwhere))
                sqlwhere = " 1=1";
            using (var db = DataContent.DBServer())
            {
                var obj = new
                {
                    TableName = "StudentTestQuestion",
                    Fields = "*",
                    OrderField = orderbyFiled,
                    sqlWhere = sqlwhere,
                    pageSize = pagesize,
                    pageIndex = pageindex,
                };

                var dynamicParam = new DynamicParameters(obj);
                dynamicParam.Add("TotalPage", null, DbType.Int32, ParameterDirection.Output, 32);
                dynamicParam.Add("TotalRecord", 0, DbType.Int32, ParameterDirection.Output, 32);

                var items = db.Query<StudentTestQuestion>("PagingRecord", dynamicParam, null, true, null, CommandType.StoredProcedure);
                totalPage = dynamicParam.Get<int>("TotalPage");
                totalRecord = dynamicParam.Get<int>("TotalRecord");
                return items.ToList();
            }
        }
        public List<PaperQuestion> GetPaperQuestionNotInStudentPaperQuestionList(StudentTestHistory sth)
        {
            using (var db = DataContent.DBServer())
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select vpq.* from View_PaperQuestion vpq where ");
                sb.Append("vpq.Paperid='{1}' and not exists (select s.QuestionID from ");
                sb.Append("StudentTestQuestion s where s.TestHistoryID='{0}' and s.QuestionID=vpq.ID) ");   

                var items = db.Query<PaperQuestion>(string.Format(sb.ToString(), sth.ID, sth.PaperInfoID));
                return items.ToList();
            }
        }
        public List<StudentTestQuestion> GetStudentPaperQuestionNotInPaperQustionList(StudentTestHistory sth)
        {
            using (var db = DataContent.DBServer())
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select distinct stq.* ");
                sb.Append("from StudentTestQuestion stq ");
                sb.Append("where stq.TestHistoryID='{0}' ");
                sb.Append("and not exists (select s.QuestionID from PaperSubjectQuestion s where s.PaperID='{1}' ");
                sb.Append("and s.QuestionID=stq.QuestionID)");

                var items = db.Query<StudentTestQuestion>(string.Format(sb.ToString(), sth.ID, sth.PaperInfoID));
                return items.ToList();
            }
        }



        public List<StudentPaperQuestion> GetListQuestionByPSID(StudentTestHistory sth, string studentid, string paperid)
        {
            using (var db = DataContent.DBServer())
            {
                string strWhere = string.Empty;
                if (sth != null && sth.ID != null)
                    strWhere = string.Format(" TestHistoryID='{0}'", sth.ID);
                else
                    strWhere = string.Format(" StudentID='{0}' and PaperInfoID='{1}'", studentid, paperid);
                
                StringBuilder sb = new StringBuilder("");
                sb.Append("select distinct * from View_StudentPaperQuestion where ");
                sb.Append(strWhere);
                sb.Append(" order by POrderIndex,PSOrderIndex,AddTime");
                var items = db.Query<StudentPaperQuestion>(sb.ToString());
                return items.ToList();            
            }
        }

        public bool AnswerUpdate(string sthid, string prefix, int actionflag)
        {
            if (sthid == null || sthid.Length == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                int retCode = 0;
                StudentTestQuestion stq = Get(new Guid(sthid));
                bool exists = stq.StudentAnswer.Trim().Contains(prefix);
                if (actionflag == 0 && exists)
                {
                    string temp = stq.StudentAnswer.Replace(prefix, "");
                    retCode = db.Execute(@"update StudentTestQuestion set StudentAnswer=@sa where ID=@id",
                                         new[] { new { sa = temp, id = sthid } });
                    flag = retCode <= 0;
                }
                else if (actionflag == 1 && !exists)
                {
                    string sta = string.Empty;
                    string[] letterlist = new string[] { "", "", "", "", "" };
                    prefix = prefix.ToUpper();
                    if (prefix.Equals("A"))
                        letterlist[0] = prefix;
                    else if (prefix.Equals("B"))
                        letterlist[1] = prefix;
                    else if (prefix.Equals("C"))
                        letterlist[2] = prefix;
                    else if (prefix.Equals("D"))
                        letterlist[3] = prefix;
                    else if (prefix.Equals("E"))
                        letterlist[4] = prefix;
                    char[] cl = stq.StudentAnswer.ToCharArray();
                    foreach (char c in cl)
                    {
                        if (c == 'A')
                            letterlist[0] = "A";
                        else if (c == 'B')
                            letterlist[1] = "B";
                        else if (c == 'C')
                            letterlist[2] = "C";
                        else if (c == 'D')
                            letterlist[3] = "D";
                        else if (c == 'E')
                            letterlist[4] = "E";
                    }
                    foreach (string str in letterlist)
                    {
                        sta += str;
                    }
                    retCode = db.Execute(@"update StudentTestQuestion set StudentAnswer=@sa where ID=@id",
                                         new[] { new { sa = sta, id = sthid } });
                    flag = retCode > 0;
                }
            }
            return flag;
        }
        public bool UpdateRad(string sthid, string prefix)
        {
            if (sthid == null || sthid.Length == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                var retCode = db.Execute(@"update StudentTestQuestion set StudentAnswer=@sa where ID=@id",
                                     new[] { new { sa = prefix, id = sthid } });
                flag = retCode > 0;
            }
            return flag;
        }
        public void UpdateActionTime(string sthid)
        {
            if (sthid == null || sthid.Length == 0) return;
            using (var db = DataContent.DBServer())
            {
                var retCode = db.Execute(@"update StudentTestHistory set TestPauseTime=@tpt where ID=@id",
                                     new[] { new { tpt = System.DateTime.Now, id = sthid } });
            }
        }

        public bool UpdateStudentAnwser(string sthid, string answer)
        {
            if (sthid == null || sthid.Length == 0) return false;
            bool flag = false;
            using (var db = DataContent.DBServer())
            {
                var retCode = db.Execute(@"update StudentTestQuestion set StudentAnswer=@sa, AnswerTime=@tpt where ID=@id",
                                     new[] { new { sa = answer, tpt = System.DateTime.Now, id = sthid } });
                flag = retCode > 0;
                UpdateActionTime(sthid);
            }
            return flag;
        }

        public bool AnswerCheck(string sthid)
        {
            using (var db = DataContent.DBServer())
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select * ");
                sb.Append("from StudentTestQuestion ");
                sb.Append("where ID='{0}' and RightAnswer=StudentAnswer ");

                var items = db.Query<StudentTestQuestion>(string.Format(sb.ToString(), sthid));
                if (items != null && items.ToList().Count > 0)
                    return true;
                return false;
            }
        }

        public decimal GetPaperTotalPoint(Guid testhistoryId)
        {
            using (var db = DataContent.DBServer())
            {
                string getTotalPointSql = string.Format("  select SUM(point) from StudentTestQuestion where TestHistoryID='{0}'", testhistoryId);
                var totalPoint = db.Query<decimal>(getTotalPointSql).ToList().FirstOrDefault();
                return totalPoint;
            }

        }

        public string GetQuestionKnowledge(string sthid)
        {
            using (var db = DataContent.DBServer())
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(" select * from DBQuestion where ID=(");
                sb.Append("select top 1 QuestionID from StudentTestQuestion where ");
                sb.Append(string.Format("ID='{0}')", sthid));

                var items = db.Query<DBQuestion>(sb.ToString());
                List<DBQuestion> list = items.ToList();
                if (list != null && list.Count > 0)
                {
                    DBQuestion dBQuestion = list[0];
                    return string.IsNullOrEmpty(dBQuestion.Knowledge) ? "" : dBQuestion.Knowledge;
                }
                return "";
            }
        }

        public string[] ResultUpdate(string sthid, string sectionid, int actionflag)
        {
            string[] resultList = new string[] { "0", "0", "0", "" };
            using (var db = DataContent.DBServer())
            {
                try
                {
                    int retCode = 0;
                    StringBuilder sb = new StringBuilder("");
                    sb.Append("update StudentTestQuestion set StudentPoint=0 where TestHistoryID=@thid; ");
                    sb.Append("update StudentTestQuestion set StudentPoint=Point ");
                    sb.Append("where RightAnswer=StudentAnswer and len(StudentAnswer)>0 ");
                    sb.Append("and Point is not null and TestHistoryID=@thid");
                    retCode = db.Execute(sb.ToString(), new[] { new { thid = sthid } });

                    sb = new StringBuilder("");
                    sb.Append("update StudentTestHistory set Status=1,TestEndTime=@tet,TestPauseTime=@tet, TotalPoint=(");
                    sb.Append("select sum(isnull(StudentPoint,0)) from StudentTestQuestion where ");
                    sb.Append("TestHistoryID=@thid) where ID=@thid");
                    retCode = db.Execute(sb.ToString(), new[] { new { tet = System.DateTime.Now, thid = sthid } });

                    StudentTestHistoryService pro = ObjectFactory<StudentTestHistoryService>.Create();
                    StudentTestHistory sth = pro.Get(new Guid(sthid));

                    if (!string.IsNullOrEmpty(sectionid))
                    {
                        #region 用户等级

                        try
                        {
                            StudentService ProxyStudent = ObjectFactory<StudentService>.Create();
                            SectionPaperService ProxySP = ObjectFactory<SectionPaperService>.Create();
                            if (!string.IsNullOrEmpty(sectionid))
                            {
                                var items = ProxySP.GetPaperInfoBySectionId(new Guid(sectionid));
                                var sectionPapter = items.FindAll(r => r.PaperID == sth.PaperInfoID).FirstOrDefault();
                                //如果sectionPapter!=null，那么取里面的TestType来判断测试类型
                                if (sectionPapter != null)
                                {
                                    int tt = sectionPapter.TestType;
                                    if (tt == 2)
                                    {
                                        //StudentLevelService.InsertOrUpdate()
                                        StudentLevel sl = new StudentLevel();
                                        sl.ID = Guid.NewGuid();
                                        string studentsql = string.Format("select * from Student where ID='{0}'",
                                            sth.StudentID);
                                        var itemsStudent = db.Query<Student>(studentsql);
                                        if (itemsStudent != null && itemsStudent.ToList().Count > 0)
                                        {
                                            Student s = itemsStudent.ToList()[0] as Student;
                                            sl.UserID = s.UserID;
                                        }
                                        // 等级
                                        string sqsql = string.Format("select * from StudentTestQuestion where TestHistoryID='{0}'",
                                            sthid);
                                        var itemssq = db.Query<StudentTestQuestion>(sqsql);
                                        if (itemssq != null && itemssq.ToList().Count > 0)
                                        {
                                            decimal all = 0;
                                            List<StudentTestQuestion> listSTQ = itemssq.ToList();
                                            foreach (StudentTestQuestion stq in listSTQ)
                                            {
                                                all += stq.Point;
                                            }
                                            all = all == 0 ? 1 : all;
                                            double tl = Convert.ToDouble((sth.TotalPoint / all) * 100);
                                            if (tl >= 90)
                                                sl.TestLevel = (int)EnumLevel.高级;
                                            else if (tl >= 70)
                                                sl.TestLevel = (int)EnumLevel.中级;
                                            else
                                                sl.TestLevel = (int)EnumLevel.初级;

                                            StudentLevelService ProxySL = ObjectFactory<StudentLevelService>.Create();
                                            ProxySL.InsertOrUpdate(sl);
                                        }
                                    }
                                }
                            }
                        }
                        catch { }

                        #endregion
                    }
                    if (sth != null && actionflag == 1)
                    {

                        int totalPage, totalRecord;
                        string strWhere1 = string.Format(" TestHistoryID='{0}' and RightAnswer=StudentAnswer", sthid);
                        List<StudentTestQuestion> li1 = GetListByPage(strWhere1, "TestHistoryID DESC", 10000, 1, out totalPage, out totalRecord);
                        string strWhere2 = string.Format(" TestHistoryID='{0}' and RightAnswer<>StudentAnswer", sthid);
                        List<StudentTestQuestion> li2 = GetListByPage(strWhere2, "TestHistoryID DESC", 10000, 1, out totalPage, out totalRecord);

                        //Alex Yu  获取题目总分
                        var questionTotalPoint = GetPaperTotalPoint((sth.ID));
                        //获取试卷总分
                        PaperInfoService PaperProxy = ObjectFactory<PaperInfoService>.Create();
                        var paperinfo = PaperProxy.Get(sth.PaperInfoID);
                        var score = sth.TotalPoint / questionTotalPoint * paperinfo.Score; //  score = 题目得分/题目总分*试卷总分 四舍五入为整数
                        resultList[0] = Math.Round(score, MidpointRounding.AwayFromZero).ToString();   

                        resultList[1] = li1 == null ? "0" : li1.Count.ToString();
                        resultList[2] = li2 == null ? "0" : li2.Count.ToString();

                        #region 评语获取
                        try
                        {
                            //sb = new StringBuilder("");
                            //sb.Append("select * from PaperFeedback where ");
                            //sb.Append("PaperFeedBackCommentID=(select top 1 PaperFeedBackCommentID from PaperInfo where ID='{0}') ");
                            //sb.Append("and MinScore<{1} and MaxScore<={1}");
                            //string str = string.Format(sb.ToString(),
                            //    sth.PaperInfoID.ToString(), sth.TotalPoint);
                            //var items = db.Query<PaperFeedback>(str);
                            //List<PaperFeedback> list = items.ToList();
                            //if (list != null && list.Count > 0)
                            //{
                            //    PaperFeedback paperFeedback = list[0];
                            //    resultList[3] = string.IsNullOrEmpty(paperFeedback.Comment) ? "" : paperFeedback.Comment;
                            //}
                            PaperFeedback paperFeedback = pro.GetStudentTestComment(sth.StudentID, sth.PaperInfoID);
                            resultList[3] = paperFeedback == null ? "" : paperFeedback.Comment;
                        }
                        catch (Exception ex)
                        {
                            resultList[3] = "";
                        }
                        #endregion
                    }
                }
                catch { }
            }
            return resultList;
        }
    }
}
